package me.helllp.dake.generator.sql;

import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import me.helllp.dake.generator.meta.third.RdbmsMetaData;
import me.helllp.dake.generator.sql.api.*;
import me.helllp.dake.generator.sql.config.GeneratedKey;
import me.helllp.dake.generator.sql.config.PropertyRegistry;
import me.helllp.dake.generator.sql.config.TableConfiguration;
import me.helllp.dake.generator.sql.internal.FullyQualifiedJavaType;
import me.helllp.dake.generator.sql.internal.JDBCConnectionFactory;
import me.helllp.dake.generator.sql.internal.db.ActualTableName;
import me.helllp.dake.generator.sql.internal.types.JavaTypeResolverDefaultImpl;
import me.helllp.dake.generator.sql.internal.util.JavaBeansUtil;

import java.sql.*;
import java.util.*;

import static me.helllp.dake.generator.sql.internal.util.StringUtility.*;

@Slf4j
public class DbMateData {
    @Getter
    private RdbmsMetaData config;


    @Getter
    private final List<IntrospectedTable> introspectedTables = new ArrayList<>();

    private DatabaseMetaData databaseMetaData;

    public DbMateData(RdbmsMetaData config) {
        this.config = config;
    }

    /**
     * 获取数据库表信息
     * mysql连接串中应包含 nullCatalogMeansCurrent=true 配置，否则有可能读取其他表的数据
     *
     * @return
     * @throws SQLException
     * @throws InterruptedException
     */
    public List<IntrospectedTable> getTablesInfo() throws SQLException, InterruptedException {
        Connection connection = this.getConnection();
        this.databaseMetaData = connection.getMetaData();

        List<IntrospectedTable> list = this.enhanceIntrospectedTable();
        this.closeConnection(connection);

        return list;
    }

    /**
     * 根据数据库连接METADATA，解析全部的表
     *
     * @return 表信息列表
     * @throws SQLException
     */
    private List<IntrospectedTable> enhanceIntrospectedTable() throws SQLException {
        List<IntrospectedTable> list = new ArrayList<>();

        JavaTypeResolver javaTypeResolver = new JavaTypeResolverDefaultImpl();

        ResultSet rs = databaseMetaData.getTables(null, "%", "%",
                new String[]{"TABLE"});
        while (rs.next()) {

            String remarks = rs.getString("REMARKS"); //$NON-NLS-1$
            String tableType = rs.getString("TABLE_TYPE"); //$NON-NLS-1$
            String tableName = rs.getString("TABLE_NAME");

            TableConfiguration tc = new TableConfiguration();
            tc.setTableName(tableName);
            tc.setRemark(remarks);
            tc.setTableType(tableType);

            Map<ActualTableName, List<IntrospectedColumn>> map = getColumns(tc);

            calculateExtraColumnInformation(map, javaTypeResolver);
            calculateIdentityColumns(tc, map);

            List<IntrospectedTable> introspectedTables = calculateIntrospectedTables(tc, map);

            Iterator<IntrospectedTable> iterator = introspectedTables.iterator();
            while (iterator.hasNext()) {
                IntrospectedTable table = iterator.next();

                if (!table.hasAnyColumns()) {
                    // add warning that the table has no columns, remove from the
                    // list
                    iterator.remove();
                } else if (!table.hasPrimaryKeyColumns()
                        && !table.hasBaseColumns()) {
                    // add warning that the table has only BLOB columns, remove from
                    // the list

                    iterator.remove();
                } else {

                }
            }
            list.addAll(introspectedTables);
        }

        closeResultSet(rs);
        return list;
    }

    private List<IntrospectedTable> calculateIntrospectedTables(TableConfiguration tc, Map<ActualTableName, List<IntrospectedColumn>> columns) {
        boolean delimitIdentifiers = tc.isDelimitIdentifiers()
                || stringContainsSpace(tc.getCatalog())
                || stringContainsSpace(tc.getSchema())
                || stringContainsSpace(tc.getTableName());

        List<IntrospectedTable> answer = new ArrayList<>();

        for (Map.Entry<ActualTableName, List<IntrospectedColumn>> entry : columns .entrySet()) {
            ActualTableName atn = entry.getKey();

            // we only use the returned catalog and schema if something was
            // actually
            // specified on the table configuration. If something was returned
            // from the DB for these fields, but nothing was specified on the
            // table
            // configuration, then some sort of DB default is being returned
            // and we don't want that in our SQL
            FullyQualifiedTable table = new FullyQualifiedTable(
                    stringHasValue(tc.getCatalog()) ? atn.getCatalog() : null,
                    stringHasValue(tc.getSchema()) ? atn.getSchema() : null,
                    atn.getTableName(),
                    tc.getDomainObjectName(),
                    tc.getAlias(),
                    isTrue(tc.getProperty(PropertyRegistry.TABLE_IGNORE_QUALIFIERS_AT_RUNTIME)),
                    tc.getProperty(PropertyRegistry.TABLE_RUNTIME_CATALOG),
                    tc.getProperty(PropertyRegistry.TABLE_RUNTIME_SCHEMA),
                    tc.getProperty(PropertyRegistry.TABLE_RUNTIME_TABLE_NAME),
                    delimitIdentifiers);

            IntrospectedTable introspectedTable = new IntrospectedTable();
            introspectedTable.setTableName(tc.getTableName());
            introspectedTable.setTableType(tc.getTableType());
            introspectedTable.setRemarks(tc.getRemark());
            introspectedTable.setFullyQualifiedTable(table);

            for (IntrospectedColumn introspectedColumn : entry.getValue()) {
                introspectedTable.addColumn(introspectedColumn);
            }

            calculatePrimaryKey(table, introspectedTable);

            enhanceIntrospectedTable(introspectedTable);

            answer.add(introspectedTable);
        }

        return answer;
    }

    private void enhanceIntrospectedTable(IntrospectedTable introspectedTable) {
        try {
            FullyQualifiedTable fqt = introspectedTable.getFullyQualifiedTable();

            ResultSet rs = databaseMetaData.getTables(fqt.getIntrospectedCatalog(), fqt.getIntrospectedSchema(),
                    fqt.getIntrospectedTableName(), null);
            if (rs.next()) {
                String remarks = rs.getString("REMARKS"); //$NON-NLS-1$
                String tableType = rs.getString("TABLE_TYPE"); //$NON-NLS-1$
                introspectedTable.setRemarks(remarks);
                introspectedTable.setTableType(tableType);
            }
            closeResultSet(rs);
        } catch (SQLException e) {

        }
    }

    private void calculatePrimaryKey(FullyQualifiedTable table, IntrospectedTable introspectedTable) {
        ResultSet rs;

        try {
            rs = databaseMetaData.getPrimaryKeys(
                    table.getIntrospectedCatalog(), table
                            .getIntrospectedSchema(), table
                            .getIntrospectedTableName());
        } catch (SQLException e) {
            return;
        }

        try {
            // keep primary columns in key sequence order
            Map<Short, String> keyColumns = new TreeMap<>();
            while (rs.next()) {
                String columnName = rs.getString("COLUMN_NAME"); //$NON-NLS-1$
                short keySeq = rs.getShort("KEY_SEQ"); //$NON-NLS-1$
                keyColumns.put(keySeq, columnName);
            }

            for (String columnName : keyColumns.values()) {
                introspectedTable.addPrimaryKeyColumn(columnName);
            }
        } catch (SQLException e) {
            // ignore the primary key if there's any error
        } finally {
            closeResultSet(rs);
        }
    }

    private void calculateExtraColumnInformation(Map<ActualTableName, List<IntrospectedColumn>> columns, JavaTypeResolver javaTypeResolver) {
        for (Map.Entry<ActualTableName, List<IntrospectedColumn>> entry : columns.entrySet()) {
            for (IntrospectedColumn introspectedColumn : entry.getValue()) {
                String calculatedColumnName = introspectedColumn.getActualColumnName();
                introspectedColumn.setJavaProperty(
                        JavaBeansUtil.getCamelCaseString(calculatedColumnName, false));
                FullyQualifiedJavaType fullyQualifiedJavaType = javaTypeResolver.calculateJavaType(introspectedColumn);
                if (fullyQualifiedJavaType != null) {
                    introspectedColumn
                            .setFullyQualifiedJavaType(fullyQualifiedJavaType);
                    introspectedColumn.setJdbcTypeName(javaTypeResolver
                            .calculateJdbcTypeName(introspectedColumn));
                } else {

                }
            }
        }
    }

    private void calculateIdentityColumns(TableConfiguration tc, Map<ActualTableName, List<IntrospectedColumn>> columns) {
        tc.getGeneratedKey().ifPresent(gk -> {
            for (Map.Entry<ActualTableName, List<IntrospectedColumn>> entry : columns.entrySet()) {
                for (IntrospectedColumn introspectedColumn : entry.getValue()) {
                    if (isMatchedColumn(introspectedColumn, gk)) {
                        if (gk.isIdentity() || gk.isJdbcStandard()) {
                            introspectedColumn.setIdentity(true);
                            introspectedColumn.setSequenceColumn(false);
                        } else {
                            introspectedColumn.setIdentity(false);
                            introspectedColumn.setSequenceColumn(true);
                        }
                    }
                }
            }
        });
    }

    private boolean isMatchedColumn(IntrospectedColumn introspectedColumn, GeneratedKey gk) {
        if (introspectedColumn.isColumnNameDelimited()) {
            return introspectedColumn.getActualColumnName().equals(gk.getColumn());
        } else {
            return introspectedColumn.getActualColumnName().equalsIgnoreCase(gk.getColumn());
        }
    }

    public Connection getConnection() throws SQLException {
        ConnectionFactory connectionFactory = new JDBCConnectionFactory(config);
        return connectionFactory.getConnection();
    }

    public void closeConnection(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                // ignore
            }
        }
    }

    /**
     * 获取字段信息
     *
     * @param tc 表配置
     * @return 表中字段信息
     * @throws SQLException
     */
    public Map<ActualTableName, List<IntrospectedColumn>> getColumns(TableConfiguration tc) throws SQLException {
        String localCatalog;
        String localSchema;
        String localTableName;

        boolean delimitIdentifiers = tc.isDelimitIdentifiers()
                || stringContainsSpace(tc.getCatalog())
                || stringContainsSpace(tc.getSchema())
                || stringContainsSpace(tc.getTableName());

        if (delimitIdentifiers) {
            localCatalog = tc.getCatalog();
            localSchema = tc.getSchema();
            localTableName = tc.getTableName();
        } else if (databaseMetaData.storesLowerCaseIdentifiers()) {
            localCatalog = tc.getCatalog() == null ? null : tc.getCatalog()
                    .toLowerCase();
            localSchema = tc.getSchema() == null ? null : tc.getSchema()
                    .toLowerCase();
            localTableName = tc.getTableName().toLowerCase();
        } else if (databaseMetaData.storesUpperCaseIdentifiers()) {
            localCatalog = tc.getCatalog() == null ? null : tc.getCatalog()
                    .toUpperCase();
            localSchema = tc.getSchema() == null ? null : tc.getSchema()
                    .toUpperCase();
            localTableName = tc.getTableName().toUpperCase();
        } else {
            localCatalog = tc.getCatalog();
            localSchema = tc.getSchema();
            localTableName = tc.getTableName();
        }

        if (tc.isWildcardEscapingEnabled()) {
            String escapeString = databaseMetaData.getSearchStringEscape();

            if (localSchema != null) {
                localSchema = escapeName(localSchema, escapeString);
            }

            localTableName = escapeName(localTableName, escapeString);
        }

        Map<ActualTableName, List<IntrospectedColumn>> answer = new HashMap<>();

        ResultSet rs = databaseMetaData.getColumns(localCatalog, localSchema, localTableName, "%");

        boolean supportsIsAutoIncrement = false;
        boolean supportsIsGeneratedColumn = false;
        ResultSetMetaData rsmd = rs.getMetaData();
        int colCount = rsmd.getColumnCount();
        for (int i = 1; i <= colCount; i++) {
            if ("IS_AUTOINCREMENT".equals(rsmd.getColumnName(i))) { //$NON-NLS-1$
                supportsIsAutoIncrement = true;
            }
            if ("IS_GENERATEDCOLUMN".equals(rsmd.getColumnName(i))) { //$NON-NLS-1$
                supportsIsGeneratedColumn = true;
            }
        }

        while (rs.next()) {
            IntrospectedColumn introspectedColumn = new IntrospectedColumn();
            introspectedColumn.setTableAlias(tc.getAlias());
            introspectedColumn.setJdbcType(rs.getInt("DATA_TYPE")); //$NON-NLS-1$
            introspectedColumn.setActualTypeName(rs.getString("TYPE_NAME")); //$NON-NLS-1$
            introspectedColumn.setLength(rs.getInt("COLUMN_SIZE")); //$NON-NLS-1$
            introspectedColumn.setActualColumnName(rs.getString("COLUMN_NAME")); //$NON-NLS-1$
            introspectedColumn
                    .setNullable(rs.getInt("NULLABLE") == DatabaseMetaData.columnNullable); //$NON-NLS-1$
            introspectedColumn.setScale(rs.getInt("DECIMAL_DIGITS")); //$NON-NLS-1$
            introspectedColumn.setRemarks(rs.getString("REMARKS")); //$NON-NLS-1$
            introspectedColumn.setDefaultValue(rs.getString("COLUMN_DEF")); //$NON-NLS-1$

            if (supportsIsAutoIncrement) {
                introspectedColumn.setAutoIncrement(
                        "YES".equals(rs.getString("IS_AUTOINCREMENT"))); //$NON-NLS-1$ //$NON-NLS-2$
            }

            if (supportsIsGeneratedColumn) {
                introspectedColumn.setGeneratedColumn(
                        "YES".equals(rs.getString("IS_GENERATEDCOLUMN"))); //$NON-NLS-1$ //$NON-NLS-2$
            }

            ActualTableName atn = new ActualTableName(
                    rs.getString("TABLE_CAT"), //$NON-NLS-1$
                    rs.getString("TABLE_SCHEM"), //$NON-NLS-1$
                    rs.getString("TABLE_NAME")); //$NON-NLS-1$

            List<IntrospectedColumn> columns = answer.computeIfAbsent(atn, k -> new ArrayList<>());

            columns.add(introspectedColumn);

        }

        closeResultSet(rs);

        if (answer.size() > 1
                && !stringContainsSQLWildcard(localSchema)
                && !stringContainsSQLWildcard(localTableName)) {
            // issue a warning if there is more than one table and
            // no wildcards were used
            ActualTableName inputAtn = new ActualTableName(tc.getCatalog(), tc
                    .getSchema(), tc.getTableName());

            StringBuilder sb = new StringBuilder();
            boolean comma = false;
            for (ActualTableName atn : answer.keySet()) {
                if (comma) {
                    sb.append(',');
                } else {
                    comma = true;
                }
                sb.append(atn.toString());
            }
        }

        return answer;
    }

    private void closeResultSet(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                // ignore
            }
        }
    }

    private String escapeName(String localName, String escapeString) {
        StringTokenizer st = new StringTokenizer(localName, "_%", true); //$NON-NLS-1$
        StringBuilder sb = new StringBuilder();
        while (st.hasMoreTokens()) {
            String token = st.nextToken();
            if (token.equals("_") //$NON-NLS-1$
                    || token.equals("%")) { //$NON-NLS-1$
                sb.append(escapeString);
            }
            sb.append(token);
        }
        return sb.toString();
    }
}
